import pymssql
from datetime import datetime

# 数据库连接信息
DB_CONFIG = {
    'server': '(local)',
    'user': 'sa',
    'password': '123456789',
    'database': 'JCDGXT',
    'charset': 'utf8'
}


# 初始化数据库连接
def get_db_connection():
    return pymssql.connect(
        server=DB_CONFIG['server'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset=DB_CONFIG['charset']
    )


# 获取所有领取信息
def all():
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT ISBN, aid, sno, number, time FROM [acceptance$]')
    result = cursor.fetchall()
    conn.close()
    return result


# 插入领取信息
def insert_acceptance(ISBN, aid, sno, number, time):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        # 确保 time 是 datetime 对象，如果不是需要进行转换
        if isinstance(time, str):
            time = datetime.strptime(time, '%Y-%m-%d %H:%M:%S')

        sql = 'INSERT INTO [acceptance$] (ISBN, aid, sno, number, time) VALUES (%s, %s, %s, %s, %s)'
        params = (ISBN, aid, sno, number, time.strftime('%Y-%m-%d %H:%M:%S'))
        cursor.execute(sql, params)
        conn.commit()
    finally:
        cursor.close()
        conn.close()


# 检查领取是否存在
def acceptance_exists(ISBN, aid, sno, time):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(
        'SELECT 1 FROM [acceptance$] WHERE ISBN = %s AND aid = %s AND sno = %s AND time = %s',
        (ISBN, aid, sno, time)
    )
    result = cursor.fetchone()
    conn.close()
    return result is not None


# 检查ISBN是否存在
def isbn_exists(ISBN):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM [jiaocai$] WHERE ISBN = %s', (ISBN,))
    result = cursor.fetchone()
    conn.close()
    return result is not None


# 检查管理员号是否存在
def aid_exists(aid):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM [administrators$] WHERE aid = %s', (aid,))
    result = cursor.fetchone()
    conn.close()
    return result is not None


# 检查学生号是否存在
def sno_exists(sno):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM [students$] WHERE sno = %s', (sno,))
    result = cursor.fetchone()
    conn.close()
    return result is not None

# 根据ISBN、aid、sno、time查询领取信息
def query_by_ISBN_aid_sno_time(ISBN, aid, sno, time):
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute(
        'SELECT * FROM [acceptance$] WHERE ISBN = %s AND aid = %s AND sno = %s AND time = %s',
        (ISBN, aid, sno, time)
    )
    result = cursor.fetchone()
    conn.close()
    return result


# 根据ISBN、aid、sno、time更新领取信息
def update_by_ISBN_aid_sno_time(ISBN, aid, sno, time, data):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        # 确保 time 是 datetime 对象，如果不是则进行转换
        if isinstance(time, str):
            time = datetime.strptime(time, '%Y-%m-%d %H:%M:%S')
        sql = 'UPDATE [acceptance$] SET number = %s WHERE ISBN = %s AND aid = %s AND sno = %s AND time = %s'
        params = (data['number'], ISBN, aid, sno, time.strftime('%Y-%m-%d %H:%M:%S'))
        cursor.execute(sql, params)
        conn.commit()
    finally:
        cursor.close()
        conn.close()


# 根据ISBN、aid、sno、time删除领取信息
def delete_by_ISBN_aid_sno_time(ISBN, aid, sno, time):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM [acceptance$] WHERE ISBN=%s AND aid=%s AND sno=%s AND time=%s',
                   (ISBN, aid, sno, time))
    conn.commit()
    conn.close()
